using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using MITI;

namespace MapControl.Misc.DAL
{
    public class Airport
    {
        public int Airport_Id;
        public string Airport_Name;
        public double Longitude;
        public double Latitude;
    }

    public class AirPortRepository
    {
        BaseDBCe2 _dbConnect;
        public AirPortRepository(string dbName)
        {
            _dbConnect = new BaseDBCe2(dbName);
        }
        
        public DataTable GetAll()
        {
            string sql = "select * from airport order by airport_name";
            return _dbConnect.ExecSql_DataSet(sql).Tables[0];
        }

        public bool IsExist(string airport_name)
        {
            string sql = string.Format("select * from airport where airport_name = N'{0}'", airport_name);
            if (_dbConnect.ExecSql_DataSet(sql).Tables[0].Rows.Count > 0)
                return true;
            return false;
        }

        public List<Airport> GetAllList()
        {
            List<Airport> airportList = new List<Airport>();
            DataTable db = GetAll();
            foreach (DataRow dr in db.Rows)
            {
                Airport objAirport = new Airport();
                objAirport.Airport_Id = int.Parse(dr["airport_id"].ToString());
                objAirport.Airport_Name = dr["airport_name"].ToString();
                objAirport.Longitude = double.Parse(dr["longitude"].ToString());
                objAirport.Latitude = double.Parse(dr["latitude"].ToString());    
                airportList.Add(objAirport);

            }
            return airportList;

        }

        public DataRow GetCurrentAirportDataRow()
        {
            string sql = "select * from airport where isActive = 1";
            DataRow dr = _dbConnect.ExecSql_DataSet(sql).Tables[0].Rows[0];
            return dr;
        }

        public Airport GetCurrentAirport()
        {
            Airport objAirport = null;
            try
            {
                DataRow dr = GetCurrentAirportDataRow();
                objAirport = new Airport();
                objAirport.Airport_Id = int.Parse(dr["airport_id"].ToString());
                objAirport.Airport_Name = dr["airport_name"].ToString();
                objAirport.Longitude = double.Parse(dr["longitude"].ToString());
                objAirport.Latitude = double.Parse(dr["latitude"].ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
            return objAirport;
        }

        public Airport GetAirportById(int airport_id)
        {
            string sql = "";
            Airport objAirport = null;
            try
            {
                sql = "select * from airport where airport_id = " + airport_id;
                DataRow dr = _dbConnect.ExecSql_DataSet(sql).Tables[0].Rows[0];
                objAirport = new Airport();
                objAirport.Airport_Id = int.Parse(dr["airport_id"].ToString());
                objAirport.Airport_Name = dr["airport_name"].ToString();
                objAirport.Longitude = double.Parse(dr["longitude"].ToString());
                objAirport.Latitude = double.Parse(dr["latitude"].ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return objAirport;
        }

        public void UpdateActiveAirport(int airport_id)
        {
            try
            {
                _dbConnect.ExecSql_NoneQuery("update airport set isActive = 0");
                //int value = isActive ? 1 : 0;
                string sql = string.Format("update airport set isActive = 1 where airport_id = {0}", airport_id);
                _dbConnect.ExecSql_NoneQuery(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        public void Insert(Airport objAirport)
        {
            string sql = "";
            try
            {
                sql = "select max(airport_id) from airport";
                objAirport.Airport_Id =int.Parse(_dbConnect.ExecSql_DataValue(sql).ToString()) + 1;
                sql = string.Format("insert into airport(airport_id, airport_name,longitude,latitude) values({0},N'{1}',{2},{3})",
                                          objAirport.Airport_Id, objAirport.Airport_Name, objAirport.Longitude, objAirport.Latitude);
                _dbConnect.ExecSql_NoneQuery(sql);
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            
        }

        public void Update(Airport objAirport)
        {
            string sql = "";
            try
            {
                sql = string.Format("update airport set airport_name=N'{0}', longitude={1}, latitude={2} where airport_id={3}",
                                        objAirport.Airport_Name, objAirport.Longitude, objAirport.Latitude, objAirport.Airport_Id);
                _dbConnect.ExecSql_NoneQuery(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete(int id)
        {
            string sql = "";
            try
            {
                sql = string.Format("delete from airport where airport_id={0}", id);
                _dbConnect.ExecSql_NoneQuery(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateAirportDistance(int airport_id, double distance)
        {
            string sql = "";
            try
            {
                sql = string.Format("update airport set distance = {0} where airport_id = {1}", distance, airport_id);
                _dbConnect.ExecSql_NoneQuery(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        
    }
}
